CREATE proc [dbo].[asi_ProcessFormula]
    (@inputFormula nvarchar(1000),
    @inputSourceTable nvarchar(200),
    @inputColumnName nvarchar(200),
    @inputColumnValue uniqueidentifier)
as
begin

set nocount on

declare @sqlStmt nvarchar(2000)
declare @substr1 nvarchar(1000)
declare @substr2 nvarchar(1000)
declare @substr3 nvarchar(1000)
declare @substrPreField nvarchar(100)
declare @substrPostField nvarchar(100)
declare @substrPreStmt nvarchar(1000)
declare @substrPostStmt nvarchar(1000)

declare @pos1 int
declare @pos2 int
declare @pos3 int
declare @pos4 int
declare @pos5 int

declare @firstchar nvarchar(1)
declare @done bit

if charindex('[', @inputFormula) > 0
begin

select @sqlStmt = 'select '

select @substrPreStmt = left (@inputFormula, 1)
if @substrPreStmt = '{' or @substrPreStmt = '['
    select @substrPreStmt = ''
else
    begin
    select @substrPreStmt = @inputFormula
    select @pos4 = charindex ('[', @substrPreStmt)
    select @pos5 = charindex ('{', @substrPreStmt)
    if @pos5 < @pos4 and @pos5 <> 0
         select @pos4 = @pos5
    select @substrPreStmt = left (@substrPreStmt, @pos4 - 1)
    end

select @sqlStmt = @sqlStmt + '''' + @substrPreStmt + '''' + ' + '

select @substrPostStmt = left (reverse(@inputFormula), 1)
if @substrPostStmt = '}' or @substrPostStmt = ']'
    select @substrPostStmt = ''
else
    begin
    select @substrPostStmt = reverse(@inputFormula)
    select @pos4 = charindex (']', @substrPostStmt)
    select @pos5 = charindex ('}', @substrPostStmt)
    if @pos5 < @pos4 and @pos5 <> 0
         select @pos4 = @pos5
    select @substrPostStmt = left (@substrPostStmt, @pos4 - 1)
    select @substrPostStmt = reverse(@substrPostStmt)
    end

select @substr1 = substring (@inputFormula, datalength (@substrPreStmt)/2 + 1,
    datalength (@inputFormula)/2 - datalength (@substrPreStmt)/2 - datalength (@substrPostStmt)/2)

select @done = 0

while @done = 0
begin
select @firstchar = left (@substr1, 1)
if @firstchar = '{'
    begin    -- find a matching curly bracket
        select @substr3 = substring (@substr1, 1, charindex ('}', @substr1))
        select @pos2 = datalength (@substr3)/2
        select @substrPreField = substring (@substr1, 2, charindex ('[', @substr1) - 2)
        select @substrPostField = substring (@substr1, charindex (']', @substr1) + 1,
            (charindex ('}', @substr1) -  charindex (']', @substr1) - 1))
        select @pos3 = (datalength (@substr3)/2) - 4 - (datalength (@substrPreField)/2) - (datalength (@substrPostField)/2)
        select @substr2 = substring (@substr3, charindex ('[', @substr3) + 1, @pos3)
        if exists (select 1 from INFORMATION_SCHEMA.COLUMNS
                where TABLE_NAME = @inputSourceTable
                and COLUMN_NAME = @substr2)
            select @sqlStmt = @sqlStmt + ' case when ' + @substr2 + ' is null then '''' else ' + '''' + @substrPreField + '''' + ' + ' + @substr2 + ' + ' + '''' + @substrPostField + '''' + ' end '  + ' + '
        else
            select @sqlStmt = @sqlStmt + '''' + @substr3 + '''' + ' + '

        select @substr1 = right (@substr1, datalength (@substr1)/2 - @pos2)
    end
else if @firstchar = '['
    begin    -- find a matching square bracket
        select @substr3 = substring (@substr1, 1, charindex (']', @substr1))
        select @substr2 = substring (@substr3, 2, datalength (@substr3)/2 -2)    

        if exists (select 1 from INFORMATION_SCHEMA.COLUMNS
            where TABLE_NAME = @inputSourceTable
            and COLUMN_NAME = @substr2)
            select @sqlStmt = @sqlStmt + ' case when ' + @substr2 + ' is null then '''' else ' + @substr2 + ' end '  + ' + '
        else
            select @sqlStmt = @sqlStmt + '''' + @substr3 + '''' + ' + '
        select @substr1 = right (@substr1, datalength (@substr1)/2 - datalength (@substr2) /2 - 2)
    end
else
    begin    -- find everything up to the next curly or square bracket, and treat it as a literal
        select @pos4 = charindex ('[', @substr1)
        select @pos5 = charindex ('{', @substr1)
        if @pos5 < @pos4 and @pos5 <> 0
             select @pos4 = @pos5
        select @substr2 = left (@substr1, @pos4)

        if (datalength (@substr2) / 2) > 0
            begin
            select @substr2 = left (@substr2, datalength (@substr2)/2 - 1)
            select @sqlStmt = @sqlStmt + '''' + @substr2 + '''' + ' + '
            end
        else
            select @done = 1
        select @substr1 = right (@substr1, datalength (@substr1)/2 - datalength (@substr2) /2)
    end

end -- while @done = 0

if substring (@sqlStmt, (datalength (@sqlStmt)/2) - 1, 1) = '+'
    select @sqlStmt = substring (@sqlStmt, 1, (datalength (@sqlStmt)/2) - 2)

if (datalength (@substrPostStmt)/2 > 0)
     select @sqlStmt = @sqlStmt + ' + ' + '''' + @substrPostStmt + ''''

-- support <n> for embedded newline, <t> for embedded tab anywhere in formula
while (charindex ('<n>', @sqlStmt) > 0)
    select @sqlStmt = replace (@sqlStmt, '<n>', ''' + char(13) + char(10) + ''')

while (charindex ('<t>', @sqlStmt) > 0)
    select @sqlStmt = replace (@sqlStmt, '<t>', ''' + char(9) + ''')

-- support <[> and <]> to embed square brackets anywhere in formula
while (charindex ('<[>', @sqlStmt) > 0)
    select @sqlStmt = replace (@sqlStmt, '<[>', '[')

while (charindex ('<]>', @sqlStmt) > 0)
    select @sqlStmt = replace (@sqlStmt, '<]>', ']')

-- support <{> and <}> to embed curly brackets anywhere in formula
while (charindex ('<{>', @sqlStmt) > 0)
    select @sqlStmt = replace (@sqlStmt, '<{>', '{')

while (charindex ('<}>', @sqlStmt) > 0)
    select @sqlStmt = replace (@sqlStmt, '<}>', '}')

select @sqlStmt = @sqlStmt + ' from ' + @inputSourceTable + ' where ' + @inputColumnName + ' = '
    + '''' + convert (nvarchar(50), @inputColumnValue) + ''''

create table #tmptbl (formattedOutput nvarchar(1000))
insert into #tmptbl  exec (@sqlStmt)

select formattedOutput from #tmptbl
end
else
select formattedOutput = @inputFormula

set nocount off

END -- create proc ProcessContactFormula

GO